#pragma once

#include "sqlite3.h"
#include <iostream>
#include <sstream>
#include <string>
#include <vector>

class Sqlite3 {
public:
    struct TableDate_t {
        int testMode;
        int testType;
        int64_t testDate;
        int testPhase;
        int testCurrent;
        double dropTime;
        int dropCause;
        int isPass;
    };

private:
    static int callback(void *userData, int col, char **value, char **colName);

private:
    const std::string SQL_PATH = "/userdata/sihdev.db";
    const std::string SQL_TABLE_NAME = "sihdev_table";

    sqlite3 *_db;

public:
    Sqlite3();

    bool insert(int testMode, int testType, uint64_t testDate, int testPhase, int testCurrent, double dropTime, int dropCause, int isPass);

    std::vector<Sqlite3::TableDate_t> selectAll(void);

    bool drop(uint64_t testDate);
};

Sqlite3::Sqlite3()
{
    int rc = sqlite3_open(SQL_PATH.c_str(), &_db);
    if (SQLITE_OK != rc) {
        std::cout << "sqlite3 open failed" << std::endl;
        return;
    }

    std::cout << "sqlite3 open success!!!!!!" << std::endl;

    std::ostringstream oss;
    oss << "create table if not exists " << SQL_TABLE_NAME << R"((testMode INTEGER, testType INTEGER, testDate BIGINT PRIMARY KEY, testPhase INTEGER, testCurrent INTEGER, dropTime DOUBLE, dropCause INTEGER, iSpass INTEGER);)";

    char *errMessage = nullptr;
    rc = sqlite3_exec(_db, oss.str().c_str(), nullptr, nullptr, &errMessage);
    if (SQLITE_OK != rc) {
        std::cout << "can't create " << SQL_TABLE_NAME << " table : " << errMessage << std::endl;
        sqlite3_free(errMessage);
        sqlite3_close(_db);
        return;
    }

    sqlite3_close(_db);
}

bool Sqlite3::insert(int testMode, int testType, uint64_t testDate, int testPhase, int testCurrent, double dropTime, int dropCause, int isPass)
{
    int rc = sqlite3_open(SQL_PATH.c_str(), &_db);
    if (SQLITE_OK != rc) {
        std::cout << "sqlite3 open failed" << std::endl;
        return false;
    }

    std::ostringstream oss;
    oss << "insert into " << SQL_TABLE_NAME << " values (" << testMode << ", " << testType << ", " << testDate << ", " << testPhase << ", " << testCurrent << ", " << dropTime << ", " << dropCause << ", " << isPass << ");";

    char *errMessage = nullptr;
    rc = sqlite3_exec(_db, oss.str().c_str(), nullptr, nullptr, &errMessage);
    if (SQLITE_OK != rc) {
        std::cout << "insert into " << SQL_TABLE_NAME << " failed" << errMessage << std::endl;
        sqlite3_free(errMessage);
        sqlite3_close(_db);
        return false;
    }

    spdlog::info("exec {} success", oss.str());

    sqlite3_close(_db);
    return true;
}

std::vector<Sqlite3::TableDate_t> Sqlite3::selectAll(void)
{
    std::vector<Sqlite3::TableDate_t> result;

    int rc = sqlite3_open(SQL_PATH.c_str(), &_db);
    if (SQLITE_OK != rc) {
        std::cout << "sqlite3 open failed" << std::endl;
        return result;
    }

    std::ostringstream oss;
    oss << "select * from " << SQL_TABLE_NAME << ";";

    char *errMessage = nullptr;
    rc = sqlite3_exec(_db, oss.str().c_str(), callback, reinterpret_cast<void *>(&result), &errMessage);
    if (SQLITE_OK != rc) {
        std::cout << "insert into " << SQL_TABLE_NAME << " failed" << std::endl;
        sqlite3_free(errMessage);
        sqlite3_close(_db);
        return result;
    }

    sqlite3_close(_db);
    return result;
}

int Sqlite3::callback(void *userData, int col, char **value, char **colName)
{
    Sqlite3::TableDate_t data;

    for (int i = 0; i < col; i++) {
        std::cout << "key : " << colName[i] << "val : " << value[i] << std::endl;
        if (std::string(colName[i]) == "testMode") {
            if (value[i]) {
                data.testMode = std::stoi(value[i]);
            }
        } else if (std::string(colName[i]) == "testType") {
            if (value[i]) {
                data.testType = std::stoi(value[i]);
            }
        } else if (std::string(colName[i]) == "testDate") {
            if (value[i]) {
                data.testDate = std::stol(value[i]);
            }
        } else if (std::string(colName[i]) == "testPhase") {
            if (value[i]) {
                data.testPhase = std::stoi(value[i]);
            }
        } else if (std::string(colName[i]) == "testCurrent") {
            if (value[i]) {
                data.testCurrent = std::stoi(value[i]);
            }
        } else if (std::string(colName[i]) == "dropTime") {
            if (value[i]) {
                data.dropTime = std::stod(value[i]);
            }
        } else if (std::string(colName[i]) == "dropCause") {
            if (value[i]) {
                data.dropCause = std::stoi(value[i]);
            }
        } else if (std::string(colName[i]) == "iSpass") {
            if (value[i]) {
                data.isPass = std::stoi(value[i]);
            }
        }
    }

    reinterpret_cast<std::vector<Sqlite3::TableDate_t> *>(userData)->push_back(data);
    return 0;
}

bool Sqlite3::drop(uint64_t testDate)
{
    int rc = sqlite3_open(SQL_PATH.c_str(), &_db);
    if (SQLITE_OK != rc) {
        std::cout << "sqlite3 open failed" << std::endl;
        return false;
    }

    std::ostringstream oss;
    oss << "delete from " << SQL_TABLE_NAME << " where testDate = " << testDate << ";";

    std::cout << "exec : " << oss.str() << std::endl;

    char *errMessage = nullptr;
    rc = sqlite3_exec(_db, oss.str().c_str(), nullptr, nullptr, &errMessage);
    if (SQLITE_OK != rc) {
        std::cout << "can't delete " << testDate << " from " << SQL_TABLE_NAME << " table : " << errMessage << std::endl;
        sqlite3_free(errMessage);
        sqlite3_close(_db);
        return false;
    }

    sqlite3_close(_db);
    return true;
}
